<?php
/**
 * Created by PhpStorm.
 * User: apple
 * Date: 2017/8/1
 * Time: 下午5:52
 */

namespace excel;


class ExcelDownload
{
    private $excel;

    // 需要导出的数据
    private $data = [];

    // 标题
    private $titles = [];

    // 文件名
    private $filename = '';
    //开始位置
    private $start = '';

    public function __construct()
    {
        Vendor("PHPExcel");
        // 创建PHPExcel对象
        $this->excel = new \PHPExcel();
        $this->excel->setActiveSheetIndex(0);
    }

    /**
     * 设置文档属性
     * @param array $properties
     * @return $this
     */
    public function setProperties($properties = [])
    {
        !empty($properties['creator']) && $this->getProperties()->setCreated($properties['creator']);
        !empty($properties['lastModifiedBy']) && $this->getProperties()->setLastModifiedBy($properties['lastModifiedBy']);
        !empty($properties['title']) && $this->getProperties()->setTitle($properties['title']);
        !empty($properties['subject']) && $this->getProperties()->setSubject($properties['subject']);
        !empty($properties['description']) && $this->getProperties()->setDescription($properties['description']);
        !empty($properties['keywords']) && $this->getProperties()->setKeywords($properties['keywords']);
        !empty($properties['category']) && $this->getProperties()->setCategory($properties['category']);

        if (!empty($properties['sheet']) && is_array($properties['sheet'])) {
            !empty($properties['sheet']['title']) && $this->getSheet()->setTitle($properties['sheet']['title']);
            !empty($properties['sheet']['rowHeight']) && $this->getSheet()->getDefaultRowDimension()->setRowHeight($properties['sheet']['rowHeight']);
        }

        return $this;
    }

    private function getProperties()
    {
        return $this->excel->getProperties();
    }

    private function getSheet()
    {
        return $this->excel->getActiveSheet();
    }

    public function setData($data)
    {
        $this->data = array_map(function ($row) {
            return array_values($row);
        }, $data);
        return $this;
    }

    public function setTitles(array $titles)
    {
        $this->titles = array_values($titles);
        return $this;
    }

    public function setFilename($filename)
    {
        $this->filename = $filename;
        return $this;
    }

    private function writeData()
    {
        $colChars = [];
        // 字段数
        $count = count($this->titles);
        for ($i = 0; $i < $count; $i++) {
            $first = floor($i / 26);
            $second = $i % 26;
            $charFirst = $first == 0 ? '' : chr(64 + $first);
            $charSecond = chr(65 + $second);
            $colChars[] = $charFirst . $charSecond;
        }
        $styleSetting = [
            'alignment' => [
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'outline' => [
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                    'color' => [
                        'rgb' => '000000',
                    ],
                ],
            ],
            'font' => [
                'name' => 'Arial',
                'size' => 10,
                'bold' => true,
            ],
            'fill' => [
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'color' => [
                    'rgb' => 'ff0000'
                ],
            ],
        ];
        // 设置表头
        for ($i = 0; $i < $count; $i++) {
            $coord = $colChars[$i] . $this->start;//设置从哪行开始
            // 设值
            $this->getSheet()->setCellValue($coord, $this->titles[$i]);
            // 设置样式
            $this->getSheet()->getStyle($coord)->applyFromArray($styleSetting);
        }
        // 设置内容
        foreach ($this->data as $row => $item) {
            foreach ($item as $k => $v) {
                if ($row === count($this->data) - 1) {
                    // 合并单元格
                    // 设值
                    $this->getSheet()->mergeCells( 'A1:'.end($colChars).'1');
                    if(isset($item[1])){
                        $this->getSheet()->mergeCells( 'A2:'.end($colChars).'2');
                        $this->getSheet()->setCellValue('A2', $item[1]);
                    }
                    $this->getSheet()->getStyle( 'A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $this->getSheet()->getStyle( 'A1')->getFont()->setBold( true);
                    $this->getSheet()->setCellValue('A1', $item[0]);
                    if(isset($item[2]) && $item[3] && $item[4] && $item[5]){
                        $this->getSheet()->setCellValue('F'.(count($this->data)+3), $item[2]);
                        $this->getSheet()->setCellValue('G'.(count($this->data)+3), $item[3]);
                        $this->getSheet()->setCellValue('O'.(count($this->data)+3), $item[4]);
                        $this->getSheet()->setCellValue('Q'.(count($this->data)+3), $item[5]);
                    }
                } else {
                    $coord = $colChars[$k] . ($row + 1 + $this->start);
                    // 设值
                    $this->getSheet()->setCellValue($coord, $v);
                    // 设置样式
                    $styleSettingTemp = $styleSetting;
                    $styleSettingTemp['fill']['color']['rgb'] = 'FFFFFF';
                    $styleSettingTemp['font']['bold'] = false;
                    $this->getSheet()->getStyle($coord)->applyFromArray($styleSettingTemp);
                }

            }

        }
    }

    private function writeData2()
    {
        $colChars = [];
        // 字段数
        $count = count($this->titles);

        for ($i = 0; $i < $count; $i++) {
            $first = floor($i / 26);
            $second = $i % 26;
            $charFirst = $first == 0 ? '' : chr(64 + $first);
            $charSecond = chr(65 + $second);
            $colChars[] = $charFirst . $charSecond;
        }


        $styleSetting = [
            'alignment' => [
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'outline' => [
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                    'color' => [
                        'rgb' => '000000',
                    ],
                ],
            ],
            'font' => [
                'name' => 'Arial',
                'size' => 10,
                'bold' => true,
            ],
            'fill' => [
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'color' => [
                    'rgb' => '99ccff'
                ],
            ],
        ];

        // 设置表头
        for ($i = 0; $i < $count; $i++) {
            $coord = $colChars[$i] . '1';
            //设宽
            if (isset($this->titles[$i]['wide'])) {
                $this->getSheet()->getColumnDimension($colChars[$i])->setWidth($this->titles[$i]['wide']);
            } else {
                $this->getSheet()->getColumnDimension($colChars[$i])->setWidth(15);
            }

            // 设值
            $this->getSheet()->setCellValue($coord, $this->titles[$i]['name']);
            // 设置样式
            $this->getSheet()->getStyle($coord)->applyFromArray($styleSetting);
        }

        // 设置内容
        foreach ($this->data as $row => $item) {
            foreach ($item as $k => $v) {
                $coord = $colChars[$k] . ($row + 2);
                // 设值
                $this->getSheet()->setCellValue($coord, $v);
                // 设置样式
                $styleSettingTemp = $styleSetting;
                $styleSettingTemp['fill']['color']['rgb'] = 'FFFFFF';
                $styleSettingTemp['font']['bold'] = false;
                $this->getSheet()->getStyle($coord)->applyFromArray($styleSettingTemp);
            }
        }

    }

    public function exec($data = [], $titles = [], $filename = '', $properties = [], $start = 1)
    {
        !empty($properties) && $this->setProperties($properties);
        !empty($titles) && $this->setTitles($titles);
        !empty($data) && $this->setData($data);
        !empty($filename) && $this->setFilename($filename);
        !empty($start) && $this->start = $start;
        $this->writeData();
        $this->download();
    }

    public function exec2($data = [], $titles = [], $filename = '', $properties = [])
    {
        !empty($properties) && $this->setProperties($properties);
        !empty($titles) && $this->setTitles($titles);
        !empty($data) && $this->setData($data);
        !empty($filename) && $this->setFilename($filename);
        $this->writeData2();
        $this->download();
    }

    /**
     * 在线浏览
     */
    private function test()
    {
        $writer = \PHPExcel_IOFactory::createWriter($this->excel, 'HTML');
        $writer->save('php://output');
    }

    private function download()
    {
        //清除缓冲区,避免乱码
        ob_end_clean();
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $this->filename . '.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
        $objWriter->save('php://output');
        die;
    }


}